Skip Headers

Oracle® Database Security Guide 10g Release 2 (10.2) Part Number B14266-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 11 Administering User Privileges, Roles, and Profiles

Many tasks, with many interwoven considerations, are involved in administering user privileges, roles, and profiles. These necessary operations and principles are discussed in the following sections:

- Managing Oracle Users

- Viewing Information About Database Users and Profiles

- Managing Resources with Profiles

- Understanding User Privileges and Roles

- Managing User Roles

- Granting User Privileges and Roles

- Revoking User Privileges and Roles

- Granting to and Revoking from the PUBLIC User Group

- When Do Grants and Revokes Take Effect?

- Granting Roles Using the Operating System or Network

- Viewing Privilege and Role Information

## 11.1 Managing Oracle Users

Each Oracle database has a list of valid database users. To access a database, a user must run a database application and connect to the database instance using a valid user name defined in the database. This section explains how to manage users for a database, and contains the following topics:

- Creating Users

- Altering Users

- Dropping Users

  See Also: Oracle Database SQL Reference for more information about SQL statements used for managing users

### 11.1.1 Creating Users

You create a database user with the `CREATE USER` statement.To create a user, you must have the `CREATE USER` system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the `CREATE USER` system privilege.

Example 11-1 creates a user and specifies the user password, default tablespace, temporary tablespace where temporary segments are created, tablespace quotas, and profile.

Example 11-1 Create a User and Grant the Create Session System Privilege

    CREATE USER jward
        IDENTIFIED BY AZ7BC2
        DEFAULT TABLESPACE data_ts
        QUOTA 100M ON test_ts
        QUOTA 500K ON data_ts
        TEMPORARY TABLESPACE temp_ts
        PROFILE clerk;
    GRANT create session TO jward;

A newly created user cannot connect to the database until granted the `CREATE SESSION` system privilege.

Note: As administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the `CONNECT` privilege did not need the additional privileges `CONNECT` used to provide. Instead, only `CREATE SESSION` was actually needed, and in fact that is the only privilege `CONNECT` presently retains. Creating its own roles gives an organization detailed control of the privileges it assigns, and protects it in case Oracle changes the roles that it defines. For example, both `CONNECT` and `RESOURCE` roles will be deprecated in future Oracle versions.

This section refers to the preceding example as it discusses the following aspects of creating a user:

- Specifying a Name

- Setting Up User Authentication

- Assigning a Default Tablespace

- Assigning Tablespace Quotas

- Assigning a Temporary Tablespace

- Specifying a Profile

- Setting Default Roles

  See Also: Granting System Privileges and Roles on page 25-11

#### 11.1.1.1 Specifying a Name

Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.

#### 11.1.1.2 Setting Up User Authentication

In Example 11-1, the new user is to be authenticated using the database. In this case, the connecting user must supply the correct password to the database to connect successfully.

Selecting and specifying the method of user authentication is discussed in "User Authentication Methods".

#### 11.1.1.3 Assigning a Default Tablespace

Each user should have a default tablespace. When a user creates a schema object and specifies no tablespace to contain it, Oracle Database stores the object in the default user tablespace.

The default setting for the default tablespaces of all users is the `SYSTEM` tablespace. If a user does not create objects, and has no privileges to do so, then this default setting is fine. However, if a user is likely to create any type of object, then you should specifically assign the user a default tablespace. Using a tablespace other than `SYSTEM` reduces contention between data dictionary objects and user objects for the same data files. In general, it is not advisable for user data to be stored in the `SYSTEM` tablespace.

You can create a permanent default tablespace other than `SYSTEM` at the time of database creation, to be used as the database default for permanent objects. By separating the user data from the system data, you reduce the likelihood of problems with the `SYSTEM` tablespace, which can in some circumstances cause the entire database to become nonfunctional. This default permanent tablespace is not used by system users, that is, `SYS`, `SYSTEM`, and `OUTLN`, whose default permanent tablespace remains `SYSTEM`. A tablespace designated as the default permanent tablespace cannot be dropped. To accomplish this goal, another tablespace must first be designated as the default permanent tablespace. It is possible to `ALTER` the default permanent tablespace to another tablespace, affecting all users or objects created after the `ALTER DDL` commits.

You can also set a user default tablespace during user creation, and change it later with the `ALTER USER` statement. Changing the user default tablespace affects only objects created after the setting is changed.

When you specify the default tablespace for a user, also specify a quota on that tablespace.

In Example 11-1, the default tablespace for user `jward` is `data_ts`, and his quota on that tablespace is `500K`.

#### 11.1.1.4 Assigning Tablespace Quotas

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does the following things:

- Users with privileges to create certain types of objects can create those objects in the specified tablespace.

- Oracle Database limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user's objects from consuming too much space in the database.

You can assign quotas to a user tablespace when you create the user, or add or change quotas later. If a new quota is less than the old one, then the following conditions hold true:

- If a user has already exceeded a new tablespace quota, then the user's objects in the tablespace cannot be allocated more space until the combined space of these objects falls below the new quota.

- If a user has not exceeded a new tablespace quota, or if the space used by the user's objects in the tablespace falls under a new tablespace quota, then the user's objects can be allocated space up to the new quota.

##### 11.1.1.4.1 Revoking User Ability to Create Objects in a Tablespace

You can revoke the ability of a user to create objects in a tablespace by changing the current quota of the user to zero. After a quota of zero is assigned, the user's objects in the tablespace remain, but new objects cannot be created and existing objects cannot be allocated any new space.

##### 11.1.1.4.2 UNLIMITED TABLESPACE System Privilege

To permit a user to use an unlimited amount of any tablespace in the database, grant the user the `UNLIMITED TABLESPACE` system privilege. This overrides all explicit tablespace quotas for the user. If you later revoke the privilege, then explicit quotas again take effect. You can grant this privilege only to users, not to roles.

Before granting the `UNLIMITED TABLESPACE` system privilege, you must consider the consequences of doing so.

Advantage:

You can grant a user unlimited access to all tablespaces of a database with one statement.

Disadvantages:

- The privilege overrides all explicit tablespace quotas for the user.

- You cannot selectively revoke tablespace access from a user with the `UNLIMITED TABLESPACE` privilege. You can grant selective or restricted access only after revoking the privilege.

#### 11.1.1.5 Assigning a Temporary Tablespace

Each user also should be assigned a temporary tablespace. When a user executes a SQL statement that requires a temporary segment, Oracle stores the segment in the user's temporary tablespace. These temporary segments are created by the system when doing sorts or joins and are owned by `SYS`, which has resource privileges in all tablespaces.

In the previous `CREATE USER` statement, the temporary tablespace of jward is `temp_ts`, a tablespace created explicitly to contain only temporary segments. Such a tablespace is created using the `CREATE TEMPORARY TABLESPACE` statement.

If the temporary tablespace of a user is not explicitly set, then the user is assigned the default temporary tablespace that was specified at database creation, or by an `ALTER DATABASE` statement at a later time. If there is no default temporary tablespace explicitly assigned, then the default is the `SYSTEM` tablespace or another permanent default established by the system administrator. It is not advisable for user data to be stored in the `SYSTEM` tablespace. Also, assigning a tablespace to be used specifically as a temporary tablespace eliminates file contention among temporary segments and other types of segments.

Note: If your `SYSTEM` tablespace is locally managed, then users must be assigned a specific default (locally managed) temporary tablespace. They may not be allowed to default to using the `SYSTEM` tablespace because temporary objects cannot be placed in permanent locally managed tablespaces.

You can set the temporary tablespace for a user at user creation, and change it later using the `ALTER USER` statement. Do not set a quota for temporary tablespaces. You can also establish tablespace groups instead of assigning individual temporary tablespaces.

See Also: The following sections in Chapter 8, Managing Tablespaces in the Oracle Database Administrator's Guide:: Temporary Tablespaces Multiple Temporary Tablespaces: Using Tablespace Groups

#### 11.1.1.6 Specifying a Profile

You also specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If no profile is specified, then the user is assigned a default profile.

See Also: "Managing Resources with Profiles"

#### 11.1.1.7 Setting Default Roles

You cannot set default roles for a user in the `CREATE USER` statement. When you first create a user, the default role setting for the user is `ALL`, which causes all roles subsequently granted to the user to be default roles. Use the `ALTER USER` statement to change the default roles for the user.

### 11.1.2 Altering Users

Users can change their own passwords. However, to change any other option of a user security domain, you must have the `ALTER USER` system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

You can alter user security settings with the `ALTER USER` statement. Changing user security settings affects the future user sessions, not current sessions.

The following statement alters the security settings for the user, `avyrros`:

    ALTER USER avyrros
        IDENTIFIED EXTERNALLY
        DEFAULT TABLESPACE data_ts
        TEMPORARY TABLESPACE temp_ts
        QUOTA 100M ON data_ts
        QUOTA 0 ON test_ts
        PROFILE clerk;

The `ALTER USER` statement here changes the security settings for the user `avyrros` as follows:

- Authentication is changed to use the operating system account of the user `avyrros`.

- The `default and temporary tablespaces are explicitly set for user avyrros`.

- `avyrros` is given a `100M` quota for the `data_ts` tablespace.

- `The quota on the test_ts is revoked for the user avyrros`.

- `avyrros` is assigned the `clerk` profile.

#### 11.1.2.1 Changing User Authentication Mechanism

Most non-DBA users can still change their own passwords with the `ALTER USER` statement, as follows:

    ALTER USER andy
       IDENTIFIED BY swordfish;

No special privileges (other than those to connect to the database) are required for a user to change passwords. Users should be encouraged to change their passwords frequently.

Users must have the `ALTER USER` privilege to switch between methods of authentication. Usually, only an administrator has this privilege.

See Also: "User Authentication Methods" for information about authentication methods that are available for Oracle Database users

#### 11.1.2.2 Changing User Default Roles

A default role is one that is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles.

See Also: "Managing User Roles"

### 11.1.3 Dropping Users

When a user is dropped, the user and associated schema are removed from the data dictionary and all schema objects contained in the user schema, if any, are immediately dropped.

Notes: If a user schema and associated objects must remain but the user must be denied access to the database, then revoke the `CREATE SESSION` privilege from the user. Do not attempt to drop the `SYS` or `SYSTEM` user. Doing so will corrupt your database.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement `ALTER SYSTEM` with the `KILL SESSION` clause.

You can drop a user from a database using the `DROP USER` statement. To drop a user and all the user schema objects (if any), you must have the `DROP USER` system privilege. Because the `DROP USER` system privilege is powerful, a security administrator is typically the only type of user that has this privilege.

If the user's schema contains any dependent schema objects, then use the `CASCADE` option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify `CASCADE` and the user schema contains dependent objects, then an error message is returned and the user is not dropped. Before dropping a user whose schema contains objects, thoroughly investigate which objects the user's schema contains and the implications of dropping them. Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

The following statement drops the user, `jones` and all associated objects and foreign keys that depend on the tables owned by `jones`.

    DROP USER jones CASCADE;

See Also: Oracle Database Administrator's Guide for more information about terminating sessions

## 11.2 Viewing Information About Database Users and Profiles

The wide variety of options that are available for viewing such information are discussed in the following subsections:

- User and Profile Information in Data Dictionary Views

- Listing All Users and Associated Information

- Listing All Tablespace Quotas

- Listing All Profiles and Assigned Limits

- Viewing Memory Use for Each User Session

### 11.2.1 User and Profile Information in Data Dictionary Views

The following data dictionary views contain information about database users and profiles:

View | Description
--- | ---
`DBA_USERS` | Describes all users of the database
`ALL_USERS` | Lists users visible to the current user, but does not describe them
`USER_USERS` | Describes only the current user
`DBA_TS_QUOTAS` `USER_TS_QUOTAS` | Describes tablespace quotas for users
`USER_PASSWORD_LIMITS` | Describes the password profile parameters that are assigned to the user
`USER_RESOURCE_LIMITS` | Displays the resource limits for the current user
`DBA_PROFILES` | Displays all profiles and their limits
`RESOURCE_COST` | Lists the cost for each resource
`V$SESSION` | Lists session information for each current session, includes user name
`V$SESSTAT` | Lists user session statistics
`V$STATNAME` | Displays decoded statistic names for the statistics shown in the `V$SESSTAT` view
`PROXY_USERS` | Describes users who can assume the identity of other users

The following sections present some examples of using these views, and assume a database in which the following statements have been executed:

    CREATE PROFILE clerk LIMIT
        SESSIONS_PER_USER 1
        IDLE_TIME 30
        CONNECT_TIME 600;

    CREATE USER jfee
        IDENTIFIED BY wildcat
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp_ts
        QUOTA 500K ON users
        PROFILE clerk;

    CREATE USER dcranney
        IDENTIFIED BY bedrock
        DEFAULT TABLESPACE users
        TEMPORARY TABLESPACE temp_ts
        QUOTA unlimited ON users;

    CREATE USER userscott
         IDENTIFIED BY scott1;

See Also: Oracle Database SQL Reference for complete descriptions of the preceding data dictionary and dynamic performance views

### 11.2.2 Listing All Users and Associated Information

The following query lists all users and their associated information as defined in the database:

    SELECT USERNAME, PROFILE, ACCOUNT_STATUS FROM DBA_USERS;

    USERNAME        PROFILE         ACCOUNT_STATUS
    --------------- --------------- ---------------
    SYS             DEFAULT         OPEN
    SYSTEM          DEFAULT         OPEN
    USERSCOTT       DEFAULT         OPEN
    JFEE            CLERK           OPEN
    DCRANNEY        DEFAULT         OPEN

All passwords are encrypted to preserve security. If a user queries the `PASSWORD` column, then that user is not able to determine the password of another user.

### 11.2.3 Listing All Tablespace Quotas

The following query lists all tablespace quotas specifically assigned to each user:

    SELECT * FROM DBA_TS_QUOTAS;

    TABLESPACE    USERNAME    BYTES     MAX_BYTES    BLOCKS    MAX_BLOCKS
    ----------    ---------  --------   ----------   -------   ----------
    USERS         JFEE              0       512000         0          250
    USERS         DCRANNEY          0           -1         0           -1

When specific quotas are assigned, the exact number is indicated in the `MAX_BYTES` column. Note that this number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by `-1`.

### 11.2.4 Listing All Profiles and Assigned Limits

The following query lists all profiles in the database and associated settings for each limit in each profile:

    SELECT * FROM DBA_PROFILES
       ORDER BY PROFILE;

    PROFILE             RESOURCE_NAME              RESOURCE   LIMIT
    -----------------   ---------------            ---------- --------------
    CLERK               COMPOSITE_LIMIT            KERNEL     DEFAULT
    CLERK               FAILED_LOGIN_ATTEMPTS      PASSWORD   DEFAULT
    CLERK               PASSWORD_LIFE_TIME         PASSWORD   DEFAULT
    CLERK               PASSWORD_REUSE_TIME        PASSWORD   DEFAULT
    CLERK               PASSWORD_REUSE_MAX         PASSWORD   DEFAULT
    CLERK               PASSWORD_VERIFY_FUNCTION   PASSWORD   DEFAULT
    CLERK               PASSWORD_LOCK_TIME         PASSWORD   DEFAULT
    CLERK               PASSWORD_GRACE_TIME        PASSWORD   DEFAULT
    CLERK               PRIVATE_SGA                KERNEL     DEFAULT
    CLERK               CONNECT_TIME               KERNEL     600
    CLERK               IDLE_TIME                  KERNEL     30
    CLERK               LOGICAL_READS_PER_CALL     KERNEL     DEFAULT
    CLERK               LOGICAL_READS_PER_SESSION  KERNEL     DEFAULT
    CLERK               CPU_PER_CALL               KERNEL     DEFAULT
    CLERK               CPU_PER_SESSION            KERNEL     DEFAULT
    CLERK               SESSIONS_PER_USER          KERNEL     1
    DEFAULT             COMPOSITE_LIMIT            KERNEL     UNLIMITED
    DEFAULT             PRIVATE_SGA                KERNEL     UNLIMITED
    DEFAULT             SESSIONS_PER_USER          KERNEL     UNLIMITED
    DEFAULT             CPU_PER_CALL               KERNEL     UNLIMITED
    DEFAULT             LOGICAL_READS_PER_CALL     KERNEL     UNLIMITED
    DEFAULT             CONNECT_TIME               KERNEL     UNLIMITED
    DEFAULT             IDLE_TIME                  KERNEL     UNLIMITED
    DEFAULT             LOGICAL_READS_PER_SESSION  KERNEL     UNLIMITED
    DEFAULT             CPU_PER_SESSION            KERNEL     UNLIMITED
    DEFAULT             FAILED_LOGIN_ATTEMPTS      PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_LIFE_TIME         PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_REUSE_MAX         PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_LOCK_TIME         PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_GRACE_TIME        PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_VERIFY_FUNCTION   PASSWORD   UNLIMITED
    DEFAULT             PASSWORD_REUSE_TIME        PASSWORD   UNLIMITED
    32 rows selected.

### 11.2.5 Viewing Memory Use for Each User Session

The following query lists all current sessions, showing the Oracle user and current User Global Area (UGA) memory use for each session:

    SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
       FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
    WHERE sess.SID = stat.SID
       AND stat.STATISTIC# = name.STATISTIC#
       AND name.NAME = 'session uga memory';

    USERNAME                       Current UGA memory
    ------------------------------ ---------------------------------------------
                                   18636bytes
                                   17464bytes
                                   19180bytes
                                   18364bytes
                                   39384bytes
                                   35292bytes
                                   17696bytes
                                   15868bytes
    USERSCOTT                      42244bytes
    SYS                            98196bytes
    SYSTEM                         30648bytes

    11 rows selected.

To see the maximum UGA memory ever allocated to each session since the instance started, replace `'session uga memory'` in the preceding query with `'session uga memory max'.`

## 11.3 Managing Resources with Profiles

A profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user. You can assign a profile to each user, and a default profile to all others. Each user can have only one profile, and creating a new one supersedes any earlier one.

Profile resource limits are enforced only when you enable resource limitation for the associated database. Enabling such limitation can occur either before starting up the database (the `RESOURCE_LIMIT` initialization parameter) or while it is open (using an `ALTER SYSTEM` statement).

While password parameters reside in profiles, they are unaffected by `RESOURCE_LIMIT` or `ALTER SYSTEM` and password management is always enabled. In Oracle Database 10g Release 2 (10.2), resource allocations and restrictions are primarily handled through the Database Resource Manager.

See Also: For resource allocation, see the Database Resource Manager, as described in the Oracle Database Administrator's Guide For password policies, see Password Management Policy For `ALTER SYSTEM` or `RESOURCE_LIMIT`, see Oracle Database SQL Reference

A profile can be created, assigned to users, altered, and dropped at any time (using CREATE USER or ALTER USER) by any authorized database user. Profiles can be assigned only to users and not to roles or other profiles. Profile assignments do not affect current sessions, instead, they take effect only in subsequent sessions.

See Also: Oracle Database SQL Reference for more information about the SQL statements used for managing profiles, such as `CREATE PROFILE`, and for information on how to calculate composite limits. Database Resource Manager in the Oracle Database Administrator's Guide "Creating Users" "Altering Users"

### 11.3.1 Dropping Profiles

To drop a profile, you must have the `DROP PROFILE` system privilege. You can drop a profile (other than the default profile) using the SQL statement `DROP PROFILE`. To successfully drop a profile currently assigned to a user, use the `CASCADE` option.

The following statement drops the profile `clerk`, even though it is assigned to a user:

    DROP PROFILE clerk CASCADE;

Any user currently assigned to a profile that is dropped is automatically assigned to the `DEFAULT` profile. The `DEFAULT` profile cannot be dropped. When a profile is dropped, the drop does not affect currently active sessions. Only sessions created after a profile is dropped abide by any modified profile assignments.

## 11.4 Understanding User Privileges and Roles

A user privilege is the right to run a particular type of SQL statement, or the right to access an object belonging to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle Database.

Roles are created by users (usually administrators) to group together privileges or other roles. They are a means of facilitating the granting of multiple privileges or roles to users.

This section describes Oracle user privileges, and contains the following topics:

- System Privileges

- Object Privileges

- User Roles

  See Also: Oracle Database Concepts for additional information about privileges and roles

### 11.4.1 System Privileges

There are over 100 distinct system privileges. Each system privilege allows a user to perform a particular database operation or class of database operations.

Caution: System privileges can be very powerful, and should be granted only when necessary to roles and trusted users of the database.

See Also: Oracle Database SQL Reference. for the complete list of system privileges and their descriptions

#### 11.4.1.1 Restricting System Privileges

Because system privileges are so powerful, Oracle recommends that you configure your database to prevent regular (non-DBA) users exercising `ANY` system privileges (such as `UPDATE ANY TABLE`) on the data dictionary. In order to secure the data dictionary, ensure that the `O7_DICTIONARY_ACCESSIBILITY` initialization parameter is set to `FALSE`, the default value. This feature is called the dictionary protection mechanism.

Note: The `O7_DICTIONARY_ACCESSIBILITY` initialization parameter controls restrictions on system privileges when you upgrade from Oracle Database version 7 to Oracle8i and higher releases. If the parameter is set to `TRUE`, then access to objects in the `SYS` schema is allowed (Oracle database version 7 behavior). If this parameter is set to `FALSE`, then system privileges that allow access to objects in any schema do not allow access to objects in the `SYS` schema. The default for `O7_DICTIONARY_ACCESSIBILITY` is `FALSE`. When this parameter is not set to `FALSE`, the `ANY` privilege applies to the data dictionary, and a malicious user with `ANY` privilege could access or alter data dictionary tables. See the Oracle Database Reference for more information on the `O7_DICTIONARY_ACCESSIBILITY` initialization parameter to understand its usage.

If you enable dictionary protection (`O7_DICTIONARY_ACCESSIBILITY` is set to `FALSE`), then access to objects in the `SYS` schema (dictionary objects) is restricted to users with the `SYS` schema. These users are `SYS` and those who connect as `SYSDBA`. System privileges providing access to objects in other schemas do not give other users access to objects in the `SYS` schema. For example, the `SELECT ANY TABLE` privilege allows users to access views and tables in other schemas, but does not enable them to select dictionary objects (base tables of dynamic performance views, views, packages, and synonyms). These users can, however, be granted explicit object privileges to access objects in the `SYS` schema.

#### 11.4.1.2 Accessing Objects in the SYS Schema

Users with explicit object privileges or those who connect with administrative privileges (`SYSDBA`) can access objects in the `SYS` schema. Another means of allowing access to objects in the `SYS` schema is by granting users any of the following roles:

- `SELECT_CATALOG_ROLE`

  This role can be granted to users to allow `SELECT` privileges on data dictionary views.

- `EXECUTE_CATALOG_ROLE`

  This role can be granted to users to allow `EXECUTE` privileges for packages and procedures in the data dictionary.

- `DELETE_CATALOG_ROLE`

  This role can be granted to users to allow them to delete records from the system audit table (`AUD$`).

Additionally, the following system privilege can be granted to users who require access to tables created in the `SYS` schema:

- `SELECT ANY DICTIONARY`

  This system privilege allows query access to any object in the `SYS` schema, including tables created in that schema. It must be granted individually to each user requiring the privilege. It is not included in `GRANT ALL PRIVILEGES`, but it can be granted through a role.

  Caution: You should grant these roles and the `SELECT ANY DICTIONARY` system privilege with extreme care, because the integrity of your system can be compromised by their misuse.

### 11.4.2 Object Privileges

Each type of object has different privileges associated with it.

You can specify `ALL` [`PRIVILEGES`] to grant or revoke all available object privileges for an object. `ALL` is not a privilege, rather, it is a shortcut, or a way of granting or revoking all object privileges with one `GRANT` and `REVOKE` statement. Note that if all object privileges are granted using the `ALL` shortcut, then individual privileges can still be revoked.

Likewise, all individually granted privileges can be revoked by specifying `ALL`. However, if you `REVOKE ALL`, and revoking causes integrity constraints to be deleted (because they depend on a `REFERENCES` privilege that you are revoking), then you must include the `CASCADE CONSTRAINTS` option in the `REVOKE` statement.

See Also: Oracle Database SQL Reference. for the complete list of object privileges

### 11.4.3 User Roles

A role groups several privileges and roles, so that they can be granted to and revoked from users simultaneously. A role must be enabled for a user before it can be used by the user.

Oracle Database provides some predefined roles to help in database administration. These roles, listed in Table 11-1, are automatically defined for Oracle databases when you run the standard scripts that are part of database creation. You can grant privileges and roles to, and revoke privileges and roles from, these predefined roles in the same way as you do with any role you define.

Note: Each installation should create its own roles and assign only those privileges that are needed. This principle enables the organization to retain detailed control of its roles and privileges. It also avoids the necessity to adjust if Oracle changes or removes Oracle-defined roles, as it has with `CONNECT`, which now has only the `CREATE SESSION` privilege. Formerly, it also had eight other privileges. Both `CONNECT` and `RESOURCE` roles will be deprecated in future Oracle versions.

Table 11-1 Predefined Roles

Role Name | Created By (Script) | Description
--- | --- | ---
`CONNECT` | `SQL.BSQ` | Includes only the following system privilege: `CREATE SESSION`
`RESOURCE` | `SQL.BSQ` | Includes the following system privileges: `CREATE CLUSTER`, `CREATE INDEXTYPE`, `CREATE OPERATOR`, `CREATE PROCEDURE`, `CREATE SEQUENCE`, `CREATE TABLE`, `CREATE TRIGGER`, `CREATE TYPE`
`DBA` | `SQL.BSQ` | All system privileges `WITH ADMIN OPTION`
`EXP_FULL_DATABASE` | `CATEXP.SQL` | Provides the privileges required to perform full and incremental database exports and includes: `SELECT ANY TABLE`, `BACKUP ANY TABLE`, `EXECUTE ANY PROCEDURE`, `EXECUTE ANY TYPE`, `ADMINISTER RESOURCE MANAGER`, and `INSERT`, `DELETE`, and `UPDATE` on the tables `SYS.INCVID`, `SYS.INCFIL`, and `SYS.INCEXP`. Also the following roles: `EXECUTE_CATALOG_ROLE` and `SELECT_CATALOG_ROLE`.
`IMP_FULL_DATABASE` | `CATEXP.SQL` | Provides the privileges required to perform full database imports. Includes an extensive list of system privileges (use view `DBA_SYS_PRIVS` to view privileges) and the following roles: `EXECUTE_CATALOG_ROLE` and `SELECT_CATALOG_ROLE`.
`DELETE_CATALOG_ROLE` | `SQL.BSQ` | Provides `DELETE` privilege on the system audit table (`AUD$`)
`EXECUTE_CATALOG_ROLE` | `SQL.BSQ` | Provides `EXECUTE` privilege on objects in the data dictionary. Also, `HS_ADMIN_ROLE`.
`SELECT_CATALOG_ROLE` | `SQL.BSQ` | Provides `SELECT` privilege on objects in the data dictionary. Also, `HS_ADMIN_ROLE`.
`RECOVERY_CATALOG_OWNER` | `CATALOG.SQL` | Provides privileges for owner of the recovery catalog. Includes: `CREATE SESSION`, `ALTER SESSION`, `CREATE SYNONYM`, `CREATE VIEW`, `CREATE DATABASE LINK`, `CREATE TABLE`, `CREATE CLUSTER`, `CREATE SEQUENCE`, `CREATE TRIGGER`, and `CREATE PROCEDURE`
`HS_ADMIN_ROLE` | `CATHS.SQL` | Used to protect access to the Heterogeneous Services (HS) data dictionary tables (grants `SELECT`) and packages (grants `EXECUTE`). It is granted to `SELECT_CATALOG_ROLE` and `EXECUTE_CATALOG_ROLE` such that users with generic data dictionary access also can access the HS data dictionary.
`AQ_USER_ROLE` | `CATQUEUE.SQL` | Obsolete, but kept mainly for release 8.0 compatibility. Provides execute privilege on `DBMS_AQ` and `DBMS_AQIN`.
`AQ_ADMINISTRATOR_ROLE` | `CATQUEUE.SQL` | Provides privileges to administer Advance Queuing. Includes `ENQUEUE ANY QUEUE`, `DEQUEUE ANY QUEUE`, and `MANAGE ANY QUEUE`, `SELECT` privileges on AQ tables and `EXECUTE` privileges on AQ packages.

Note: The first three roles in Table 11-1 namely, `CONNECT`, `RESOURCE`, and `DBA`, are provided to maintain compatibility with previous versions of Oracle and may not be created automatically in future versions of Oracle. Oracle recommends that you design your own roles for database security, rather than relying on these roles.

If you install other options or products, then other predefined roles may be created.

## 11.5 Managing User Roles

This section describes aspects of managing roles, and contains the following topics:

- Creating a Role

- Specifying the Type of Role Authorization

- Dropping Roles

### 11.5.1 Creating a Role

You can create a role using the `CREATE ROLE` statement, but you must have the `CREATE ROLE` system privilege to do so. Typically, only security administrators have this system privilege.

Note: Immediately after creation, a role has no privileges associated with it. To associate privileges with a new role, you must grant privileges or other roles to the new role.

You must give each role you create a unique name among existing user names and role names of the database. Roles are not contained in the schema of any user. In a database that uses a multibyte character set, Oracle recommends that each role name contain at least one single-byte character. If a role name contains only multibyte characters, then the encrypted role name and password combination is considerably less secure.

The following statement creates the `clerk` role, which is authorized by the database using the password `bicentennial`:

    CREATE ROLE clerk IDENTIFIED BY bicentennial;

The `IDENTIFIED BY` clause specifies how the user must be authorized before the role can be enabled for use by a specific user to which it has been granted. If this clause is not specified, or `NOT IDENTIFIED` is specified, then no authorization is required when the role is enabled. Roles can be specified to be authorized by:

- The database using a password

- An application using a specified package

- Externally by the operating system, network, or other external source

- Globally by an enterprise directory service

These authorizations are discussed in following sections.

Later, you can set or change the authorization method for a role using the `ALTER ROLE` statement. The following statement alters the `clerk` role to specify that the user must have been authorized by an external source before enabling the role:

    ALTER ROLE clerk IDENTIFIED EXTERNALLY;

To alter the authorization method for a role, you must have the `ALTER ANY ROLE` system privilege or have been granted the role with the `ADMIN OPTION`.

See Also: Oracle Database SQL Reference for syntax, restrictions, and authorization information about the SQL statements used to manage roles and privileges Oracle Database Advanced Security Administrator's Guide

### 11.5.2 Specifying the Type of Role Authorization

The methods of authorizing roles are presented in this section. A role must be enabled for you to use it.

See Also: "When Do Grants and Revokes Take Effect?" for a discussion about enabling roles

#### 11.5.2.1 Role Authorization by the Database

The usage of a role authorized by the database can be protected by an associated password. If you are granted a role protected by a password, then you can enable or disable the role by supplying the proper password for the role in a `SET ROLE` statement. However, if the role is made a default role and enabled at connect time, then the user is not required to enter a password.

The following statement creates a role called `manager`. When it is enabled, the password `morework` must be supplied.

    CREATE ROLE manager IDENTIFIED BY morework;

Note: In a database that uses a multibyte character set, passwords for roles must include only singlebyte characters. Multibyte characters are not accepted in passwords. See the Oracle Database SQL Reference for information about specifying valid passwords.

#### 11.5.2.2 Role Authorization by an Application

The `INDENTIFIED USING` `package_name` clause lets you create an application role, which is a role that can be enabled only by applications using an authorized package. Application developers do not need to secure a role by embedding passwords inside applications. Instead, they can create an application role and specify which PL/SQL package is authorized to enable the role.

The following example indicates that the role `admin_role` is an application role and the role can only be enabled by any module defined inside the PL/SQL package `hr.admin`.

    CREATE ROLE admin_role IDENTIFIED USING hr.admin;

When enabling the default roles of the user at login as specified in the user profile, no checking is performed for application roles.

#### 11.5.2.3 Role Authorization by an External Source

The following statement creates a role named `accts_rec` and requires that the user be authorized by an external source before it can be enabled:

    CREATE ROLE accts_rec IDENTIFIED EXTERNALLY;

##### 11.5.2.3.1 Role Authorization by the Operating System

Role authentication through the operating system is useful only when the operating system is able to dynamically link operating system privileges with applications. When a user starts an application, the operating system grants an operating system privilege to the user. The granted operating system privilege corresponds to the role associated with the application. At this point, the application can enable the application role. When the application is terminated, the previously granted operating system privilege is revoked from the operating system account of the user.

If a role is authorized by the operating system, then you must configure information for each user at the operating system level. This operation is operating system dependent.

If roles are granted by the operating system, then you do not need to have the operating system authorize them also. This is redundant.

See Also: "Granting Roles Using the Operating System or Network" for more information about roles granted by the operating system

##### 11.5.2.3.2 Role Authorization and Network Clients

If users connect to the database over Oracle Net, then by default, their roles cannot be authenticated by the operating system. This includes connections through a shared server configuration, as this connection requires Oracle Net. This restriction is the default because a remote user could impersonate another operating system user over a network connection.

If you are not concerned with this security risk and want to use operating system role authentication for network clients, then set the initialization parameter `REMOTE_OS_ROLES` in the database initialization parameter file to `TRUE`. The change will take effect the next time you start the instance and mount the database. The parameter is `FALSE` by default.

#### 11.5.2.4 Role Authorization by an Enterprise Directory Service

A role can be defined as a global role, where a (global) user can only be authorized to use the role by an enterprise directory service. You define the global role locally in the database by granting privileges and roles to it, but you cannot grant the global role itself to any user or other role in the database. When a global user attempts to connect to the database, the enterprise directory is queried to obtain any global roles associated with the user.

The following statement creates a global role:

    CREATE ROLE supervisor IDENTIFIED GLOBALLY;

Global roles are one component of enterprise user security. A global role only applies to one database, but it can be granted to an enterprise role defined in the enterprise directory. An enterprise role is a directory structure which contains global roles on multiple databases, and which can be granted to enterprise users.

A general discussion of global authentication and authorization of users, and its role in enterprise user management, was presented earlier in "Global Authentication and Authorization".

See Also: Oracle Database Advanced Security Administrator's Guide and Oracle Internet Directory Administrator's Guide for information about enterprise user management and how to implement it

### 11.5.3 Dropping Roles

In some cases, it may be appropriate to drop a role from the database. The security domains of all users and roles granted a dropped role are immediately changed to reflect the absence of the dropped role privileges. All indirectly granted roles of the dropped role are also removed from affected security domains. Dropping a role automatically removes the role from all user default role lists.

Because the creation of objects is not dependent on the privileges received through a role, tables and other objects are not dropped when a role is dropped.

You can drop a role using the SQL statement `DROP ROLE`. To drop a role, you must have the `DROP ANY ROLE` system privilege or have been granted the role with the `ADMIN OPTION`.

The following statement drops the role `CLERK`:

    DROP ROLE clerk;

## 11.6 Granting User Privileges and Roles

This section describes the granting of privileges and roles, and contains the following topics:

- Granting System Privileges and Roles

- Granting Object Privileges

- Granting Privileges on Columns

It is also possible to grant roles to a user connected through a middle tier or proxy. This is discussed in "Proxy Authentication and Authorization".

### 11.6.1 Granting System Privileges and Roles

You can grant system privileges and roles to other users and roles using the `GRANT` statement. The following privileges are required:

- To grant a system privilege, you must have been granted the system privilege with the `ADMIN OPTION` or have been granted the `GRANT ANY PRIVILEGE` system privilege.

- To grant a role, you must have been granted the role with the `ADMIN OPTION` or have been granted the `GRANT ANY ROLE` system privilege.

The following statement grants the system privilege `CREATE SESSION` and the `accts_pay` role to the user `jward`:

    GRANT CREATE SESSION, accts_pay TO jward;

Note: Object privileges cannot be granted along with system privileges and roles in the same `GRANT` statement.

#### 11.6.1.1 Granting the ADMIN OPTION

A user or role that is granted a privilege or role, which specifies the `WITH ADMIN OPTION` clause, has several expanded capabilities:

- The grantee can grant or revoke the system privilege or role to or from any user or other role in the database. Users cannot revoke a role from themselves.

- The grantee can further grant the system privilege or role with the `ADMIN OPTION`.

- The grantee of a role can alter or drop the role.

In the following statement, the security administrator grants the `new_dba` role to `michael`:

    GRANT new_dba TO michael WITH ADMIN OPTION;

The user `michael` cannot only use all of the privileges implicit in the `new_dba` role, but can also grant, revoke, and drop the `new_dba` role as deemed necessary. Because of these powerful capabilities, exercise caution when granting system privileges or roles with the `ADMIN OPTION`. Such privileges are usually reserved for a security administrator and rarely granted to other administrators or users of the system.

Note: When a user creates a role, the role is automatically granted to the creator with the `ADMIN OPTION`

#### 11.6.1.2 Creating a New User with the GRANT Statement

Oracle enables you to create a new user with the `GRANT` statement. If you specify a password using the `IDENTIFIED BY` clause, and the user name/password does not exist in the database, then a new user with that user name and password is created. The following example creates `ssmith` as a new user while granting `ssmith` the `CONNECT` system privilege:

    GRANT CONNECT TO ssmith IDENTIFIED BY p1q2r3;

See Also: "Creating Users"

### 11.6.2 Granting Object Privileges

You also use the `GRANT` statement to grant object privileges to roles and users. To grant an object privilege, you must fulfill one of the following conditions:

- You own the object specified.

- You possess the `GRANT ANY OBJECT PRIVILEGE` system privilege that enables you to grant and revoke privileges on behalf of the object owner.

- The `WITH GRANT OPTION` clause was specified when you were granted the object privilege by its owner.

  Note: System privileges and roles cannot be granted along with object privileges in the same `GRANT` statement.

The following statement grants the `SELECT`, `INSERT`, and `DELETE` object privileges for all columns of the `emp` table to the users, `jfee` and `tsmith`:

    GRANT SELECT, INSERT, DELETE ON emp TO jfee, tsmith;

To grant all object privileges on the `salary` view to the user `jfee`, use the `ALL` keyword as shown in the following example:

    GRANT ALL ON salary TO jfee;

Note: A grantee cannot regrant access to objects unless the original grant included the `GRANT OPTION`. Thus in the example just given, `jfee` cannot use the `GRANT` statement to grant object privileges to anyone else.

#### 11.6.2.1 Specifying the GRANT OPTION

Specify `WITH GRANT OPTION` to enable the grantee to grant the object privileges to other users and roles. The user whose schema contains an object is automatically granted all associated object privileges with the `GRANT OPTION`. This special privilege allows the grantee several expanded privileges:

- The grantee can grant the object privilege to any users in the database, with or without the `GRANT OPTION`, and to any role in the database.

- If both of the following conditions are true, then the grantee can create views on the table and grant the corresponding privileges on the views to any user or role in the database.

  - The grantee receives object privileges for the table with the `GRANT OPTION`.

  - The grantee has the `CREATE VIEW` or `CREATE ANY VIEW` system privilege.

Note: The `GRANT OPTION` is not valid when granting an object privilege to a role. Oracle Database prevents the propagation of object privileges through roles so that grantees of a role cannot propagate object privileges received by means of roles.

#### 11.6.2.2 Granting Object Privileges on Behalf of the Object Owner

The `GRANT ANY OBJECT PRIVILEGE` system privilege allows users to grant and revoke any object privilege on behalf of the object owner. This privilege provides a convenient means for database and application administrators to grant access to objects in any schema without requiring that they connect to the schema. Login credentials need not be maintained for schema owners who have this privilege, which reduces the number of connections required during configuration.

This system privilege is part of the Oracle supplied `DBA` role and is thus granted (with the `ADMIN OPTION`) to any user connecting `AS SYSDBA` (user `SYS`). As with other system privileges, the `GRANT ANY OBJECT PRIVILEGE` system privilege can only be granted by a user who possesses the `ADMIN OPTION`.

The recorded grantor of access rights to an object is either the object owner or the person exercising the `GRANT ANY OBJECT PRIVILEGE` system privilege. If the grantor with `GRANT ANY OBJECT PRIVILEGE` does not have the object privilege with the `GRANT OPTION`, then the object owner is shown as the grantor. Otherwise, when that grantor has the object privilege with the `GRANT OPTION`, then that grantor is recorded as the grantor of the grant.

Note: The audit record generated by the `GRANT` statement always shows the real user who performed the grant.

For example, consider the following. User `adams` possesses the `GRANT ANY OBJECT PRIVILEGE` system privilege. He does not possess any other grant privileges. He issues the following statement:

    GRANT SELECT ON hr.employees TO blake WITH GRANT OPTION;

If you examine the `DBA_TAB_PRIVS` view, then you will see that `hr` is shown as the grantor of the privilege:

    SQL> SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
      2>     FROM DBA_TAB_PRIVS
      3>     WHERE TABLE_NAME = 'EMPLOYEES' and OWNER = 'HR';

    GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
    -------- ----- ------- -----------  ----------
    BLAKE    HR    HR       SELECT      YES

Now assume that the user `blake` also has the `GRANT ANY OBJECT PRIVILEGE` system. He, issues the following statement:

    GRANT SELECT ON hr.employees TO clark;

In this case, when you query the `DBA_TAB_PRIVS` view again, you see that `blake` is shown as being the grantor of the privilege:

    GRANTEE  OWNER GRANTOR  PRIVILEGE    GRANTABLE
    -------- ----- -------- --------     ----------
    BLAKE    HR    HR       SELECT       YES
    CLARK    HR    BLAKE    SELECT       NO

This occurs because `blake` already possesses the `SELECT` privilege on `hr.employees` with the `GRANT OPTION`.

See Also: "Revoking Object Privileges on Behalf of the Object Owner"

#### 11.6.2.3 Granting Privileges on Columns

You can grant `INSERT`, `UPDATE`, or `REFERENCES` privileges on individual columns in a table.

Caution: Before granting a column-specific `INSERT` privilege, determine if the table contains any columns on which `NOT NULL` constraints are defined. Granting selective insert capability without including the `NOT NULL` columns prevents the user from inserting any rows into the table. To avoid this situation, make sure that each `NOT NULL` column can either be inserted into or has a non-`NULL` default value. Otherwise, the grantee will not be able to insert rows into the table and will receive an error.

The following statement grants `INSERT` privilege on the `acct_no` column of the `accounts` table to `scott`:

    GRANT INSERT (acct_no) ON accounts TO scott;

In another example, object privilege for the `ename` and `job` columns of the `emp` table are granted to the users `jfee` and `tsmith`:

    GRANT INSERT(ename, job) ON emp TO jfee, tsmith;

#### 11.6.2.4 Row-Level Access Control

You can also provide access control at the row level, that is, within objects, using Virtual Private Database (VPD) or Oracle Label Security.

See Also: Chapter 14, "Using Virtual Private Database to Implement Application Security Policies" Adding Policies for Column-Level VPD, in Chapter 15, "Implementing Application Context and Fine-Grained Access Control"

## 11.7 Revoking User Privileges and Roles

This section describes aspects of revoking user privileges and roles, and contains the following topics:

- Revoking System Privileges and Roles

- Revoking Object Privileges

- Cascading Effects of Revoking Privileges

### 11.7.1 Revoking System Privileges and Roles

You can revoke system privileges and roles using the SQL statement `REVOKE`. Any user with the `ADMIN OPTION` for a system privilege or role can revoke the privilege or role from any other database user or role. The revoker does not have to be the user that originally granted the privilege or role. Users with `GRANT ANY ROLE` can revoke any role.

The following statement revokes the `CREATE TABLE` system privilege and the `accts_rec` role from `tsmith`:

    REVOKE CREATE TABLE, accts_rec FROM tsmith;

Note: The `ADMIN OPTION` for a system privilege or role cannot be selectively revoked. The privilege or role must be revoked and then the privilege or role regranted without the `ADMIN OPTION`.

### 11.7.2 Revoking Object Privileges

To revoke an object privilege, you must fulfill one of the following conditions:

- You previously granted the object privilege to the user or role.

- You possess the `GRANT ANY OBJECT PRIVILEGE` system privilege that enables you to grant and revoke privileges on behalf of the object owner.

You can only revoke the privileges that you, the grantor, directly authorized, not the grants made by other users to whom you granted the `GRANT OPTION`. However, there is a cascading effect. The object privilege grants propagated using the `GRANT OPTION` are revoked if a grantor object privilege is revoked.

Assuming you are the original grantor, the following statement revokes the `SELECT` and `INSERT` privileges on the `emp` table from the users `jfee` and `tsmith`:

    REVOKE SELECT, insert ON emp FROM jfee, tsmith;

The following statement revokes all object privileges for the `dept` table that you originally granted to the `human_resource` role

    REVOKE ALL ON dept FROM human_resources;

Note: The `GRANT OPTION` for an object privilege cannot be selectively revoked. The object privilege must be revoked and then regranted without the `GRANT OPTION`. Users cannot revoke object privileges from themselves.

#### 11.7.2.1 Revoking Object Privileges on Behalf of the Object Owner

The `GRANT ANY OBJECT PRIVILEGE` system privilege enables you to revoke any specified object privilege where the object owner is the grantor. This occurs when the object privilege is granted by the object owner, or on behalf of the owner by any user holding the `GRANT ANY OBJECT PRIVILEGE` system privilege.

In a situation where the object privilege has been granted by both the owner of the object and the user executing the `REVOKE` statement (who has both the specific object privilege and the `GRANT ANY OBJECT PRIVILEGE` system privilege), Oracle only revokes the object privilege granted by the user issuing the `REVOKE`. This can be illustrated by continuing the example started in "Granting Object Privileges on Behalf of the Object Owner".

At this point, `blake` has granted the `SELECT` privilege on `hr.employees` to `clark`. Even though `blake` possesses the `GRANT ANY OBJECT PRIVILEGE` system privilege, he also holds the specific object privilege, thus this grant is attributed to him. Assume that `hr` also grants the `SELECT` privilege on `hr.employees` to `clark`. A query of the `DBA_TAB_PRIVS` view shows that the following grants are in effect for the hr.employees table:

    GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
    -------- ----- ------- -----------  ----------
    BLAKE    HR    HR       SELECT       YES
    CLARK    HR    BLAKE    SELECT       NO
    CLARK    HR    HR       SELECT       NO

User `blake` now issues the following `REVOKE` statement:

    REVOKE  SELECT ON hr.employees FROM clark;

Only the object privilege for `clark` granted by `blake` is removed. The grant by the object owner, `hr`, remains.

    GRANTEE  OWNER GRANTOR PRIVILEGE    GRANTABLE
    -------- ----- ------- -----------  ----------
    BLAKE    HR    HR       SELECT      YES
    CLARK    HR    HR       SELECT      NO

If `blake` issues the `REVOKE` statement again, then this time the effect will be to remove the object privilege granted by `hr`.

See Also: "Granting Object Privileges on Behalf of the Object Owner"

#### 11.7.2.2 Revoking Column-Selective Object Privileges

Although users can grant column-selective `INSERT`, `UPDATE`, and `REFERENCES` privileges for tables and views, they cannot selectively revoke column-specific privileges with a similar `REVOKE` statement. Instead, the grantor must first revoke the object privilege for all columns of a table or view, and then selectively regrant the column-specific privileges that should remain.

For example, assume that role `human_resources` has been granted the `UPDATE` privilege on the `deptno` and `dname` columns of the table `dept`. To revoke the `UPDATE` privilege on just the `deptno` column, issue the following two statements:

    REVOKE UPDATE ON dept FROM human_resources;
    GRANT UPDATE (dname) ON dept TO human_resources;

The `REVOKE` statement revokes `UPDATE` privilege on all columns of the `dept` `table from the role human_resources``. The GRANT` statement then regrants `UPDATE` privilege on `the dname` column to the role `human_resources`.

#### 11.7.2.3 Revoking the REFERENCES Object Privilege

If the grantee of the `REFERENCES` object privilege has used the privilege to create a foreign key constraint (that currently exists), then the grantor can revoke the privilege only by specifying the `CASCADE CONSTRAINTS` option in the `REVOKE` statement:

    REVOKE REFERENCES ON dept FROM jward CASCADE CONSTRAINTS;

Any foreign key constraints currently defined that use the revoked `REFERENCES` privilege are dropped when the `CASCADE CONSTRAINTS` clause is specified.

### 11.7.3 Cascading Effects of Revoking Privileges

Depending on the type of privilege, there may be cascading effects when a privilege is revoked. This is discussed in the following sections:

- System Privileges

- Object Privileges

#### 11.7.3.1 System Privileges

There are no cascading effects when revoking a system privilege related to DDL operations, regardless of whether the privilege was granted with or without the `ADMIN OPTION`. For example, assume the following:

1. The security administrator grants the `CREATE TABLE` system privilege to `jfee` with the `ADMIN OPTION`.

2. User `jfee` creates a table.

3. User `jfee` grants the `CREATE TABLE` system privilege to `tsmith`.

4. User `tsmith` creates a table.

5. The security administrator revokes the `CREATE TABLE` system privilege from `jfee`.

6. The table created by user `jfee` continues to exist. `tsmith` still has the table and the `CREATE TABLE` system privilege.

Cascading effects can be observed when revoking a system privilege related to a DML operation. If `SELECT ANY TABLE` is revoked from a user, then all procedures contained in the users schema relying on this privilege will fail until the privilege is reauthorized.

#### 11.7.3.2 Object Privileges

Revoking an object privilege can have cascading effects that should be investigated before issuing a `REVOKE` statement.

- Object definitions that depend on a DML object privilege can be affected if the DML object privilege is revoked. For example, assume that the procedure body of the `test` procedure includes a SQL statement that queries data from the `emp` table. If the `SELECT` privilege on the `emp` table is revoked from the owner of the `test` procedure, then the procedure can no longer be executed successfully.

- When a `REFERENCES` privilege for a table is revoked from a user, any foreign key integrity constraints that are defined by the user and require the dropped `REFERENCES` privilege are automatically dropped. For example, assume that the user `jward` is granted the `REFERENCES` privilege for the `deptno` column of the `dept` table. This user now creates a foreign key on the `deptno` column in the `emp` table that references the `deptno` column of the `dept` table. If the `REFERENCES` privilege on the `deptno` column of the `dept` table is revoked, then the foreign key constraint on the `deptno` column of the `emp` table is dropped in the same operation.

- The object privilege grants propagated using the `GRANT OPTION` are revoked if the object privilege of a grantor is revoked. For example, assume that `user1` is granted the `SELECT` object privilege with the `GRANT OPTION`, and grants the `SELECT` privilege on `emp` to `user2`. Subsequently, the `SELECT` privilege is revoked from `user1`. This `REVOKE` is cascaded to `user2` as well. Any objects that depend on the revoked `SELECT` privilege of `user1` and `user2` can also be affected, as described earlier.

Object definitions that require the `ALTER` and `INDEX DDL` object privileges are not affected if the `ALTER` or `INDEX` object privilege is revoked. For example, if the `INDEX` privilege is revoked from a user that created an index on a table that belongs to another user, then the index continues to exist after the privilege is revoked.

## 11.8 Granting to and Revoking from the PUBLIC User Group

Privileges and roles can also be granted to and revoked from the user group `PUBLIC`. Because `PUBLIC` is accessible to every database user, all privileges and roles granted to `PUBLIC` are accessible to every database user.

Security administrators and database users should grant a privilege or role to `PUBLIC` only if every database user requires the privilege or role. This recommendation reinforces the general rule that at any given time, each database user should have only the privileges required to accomplish the current group tasks successfully.

Revoking a privilege from `PUBLIC` can cause significant cascading effects. If any privilege related to a DML operation is revoked from `PUBLIC` (for example, `SELECT ANY TABLE, UPDATE ON emp`), then all procedures in the database, including functions and packages, must be reauthorized before they can be used again. Therefore, exercise caution when granting and revoking DML-related privileges to or from `PUBLIC`.

See Also: Managing Object Dependencies in Oracle Database Administrator's Guide for more information about object dependencies A Security Checklist, in Chapter 7, "Security Policies"

## 11.9 When Do Grants and Revokes Take Effect?

Depending on what is granted or revoked, a grant or revoke takes effect at different times:

- All grants and revokes of system and object privileges to anything (users, roles, and `PUBLIC`) take immediate effect.

- All grants and revokes of roles to anything (users, other roles, `PUBLIC`) take effect only when a current user session issues a `SET ROLE` statement to reenable the role after the grant and revoke, or when a new user session is created after the grant and revoke.

You can see which roles are currently enabled by examining the `SESSION_ROLES` data dictionary view.

### 11.9.1 The SET ROLE Statement

During the session, the user or an application can use the `SET ROLE` statement any number of times to change the roles currently enabled for the session. You must already have been granted the roles that you name in the `SET ROLE` statement`.` The number of roles that can be concurrently enabled is limited by the initialization parameter `MAX_ENABLED_ROLES`.

This example enables the role `clerk`, which you have already been granted, and specifies the password.

    SET ROLE clerk IDENTIFIED BY bicentennial;

You can disable all roles with the following statement:

    SET ROLE NONE;

### 11.9.2 Specifying Default Roles

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the default roles of the user.

A list of default roles for a user can be set and altered using the `ALTER USER` statement. The `ALTER USER` statement enables you to specify roles that are to be enabled when a user connects to the database, without requiring the user to specify the role passwords. The user must have already been directly granted the roles with a `GRANT` statement. You cannot specify as a default role any role managed by an external service including a directory service (external roles or global roles).

The following example establishes default roles for user `jane`:

    ALTER USER jane DEFAULT ROLE payclerk, pettycash;

You cannot set default roles for a user in the `CREATE USER` statement. When you first create a user, the default user role setting is `ALL`, which causes all roles subsequently granted to the user to be default roles. Use the `ALTER USER` statement to limit the default user roles.

Caution: When you create a role (other than a user role), it is granted to you implicitly and added as a default role. You receive an error at login if you have more than `MAX_ENABLED_ROLES`. You can avoid this error by altering the default user roles to be less than `MAX_ENABLED_ROLES`. Therefore, you should change the `DEFAULT ROLE` settings of `SYS` and `SYSTEM` before creating user roles.

### 11.9.3 Restricting the Number of Roles that a User Can Enable

A user can enable as many roles as specified by the initialization parameter `MAX_ENABLED_ROLES`. All indirectly granted roles enabled as a result of enabling a primary role are included in this count. The database administrator can alter this limitation by modifying the value for this parameter. Higher values permit each user session to have more concurrently enabled roles, but these values also cause more memory to be used for each user session. This occurs because the PGA size requires four bytes for each role in each session. Determine the highest number of roles that will be concurrently enabled by any one user and use this value for the `MAX_ENABLED_ROLES` parameter.

## 11.10 Granting Roles Using the Operating System or Network

This section describes aspects of granting roles through your operating system or network, and contains the following topics:

- Using Operating System Role Identification

- Using Operating System Role Management

- Granting and Revoking Roles When OS_ROLES=TRUE

- Enabling and Disabling Roles When OS_ROLES=TRUE

- Using Network Connections with Operating System Role Management

Instead of a security administrator explicitly granting and revoking database roles to and from users using `GRANT` and `REVOKE` statements, the operating system that operates Oracle can grant roles to users at connect time. Roles can be administered using the operating system and passed to Oracle Database when a user creates a session. As part of this mechanism, the default roles of a user and the roles granted to a user with the `ADMIN OPTION` can be identified. If the operating system is used to authorize users for roles, then all roles must be created in the database and privileges assigned to the role with `GRANT` statements.

Roles can also be granted through a network service.

The advantage of using the operating system to identify the database roles of a user is that privilege management for an Oracle database can be externalized. The security facilities offered by the operating system control user privileges. This option may offer advantages of centralizing security for a number of system activities, such as the following situation:

- MVS Oracle administrators want RACF groups to identify database user roles.

- UNIX Oracle administrators want UNIX groups to identify database user roles.

- VMS Oracle administrators want to use rights identifiers to identify database user roles.

The main disadvantage of using the operating system to identify the database roles of a user is that privilege management can only be performed at the role level. Individual privileges cannot be granted using the operating system, but can still be granted inside the database using `GRANT` statements.

A secondary disadvantage of using this feature is that, by default, users cannot connect to the database through the shared server or any other network connection if the operating system is managing roles. However, you can change this default as described in "Using Network Connections with Operating System Role Management".

Note: The features described in this section are available only on some operating systems. See your operating system specific Oracle documentation to determine if you can use these features.

### 11.10.1 Using Operating System Role Identification

To operate a database so that it uses the operating system to identify each user's database roles when a session is created, set the initialization parameter `OS_ROLES` to `TRUE` (and restart the instance, if it is currently running). When a user attempts to create a session with the database, Oracle Database initializes the user security domain using the database roles identified by the operating system.

To identify database roles for a user, each Oracle user's operating system account must have operating system identifiers (these may be called groups, rights identifiers, or other similar names) that indicate which database roles are to be available for the user. Role specification can also indicate which roles are the default roles of a user and which roles are available with the `ADMIN OPTION`. No matter which operating system is used, the role specification at the operating system level follows the format:

    ora_ID_ROLE[_[d][a]]

where:

- `ID` has a definition that varies on different operating systems. For example, on VMS, `ID` is the instance identifier of the database, on MVS, it is the machine type, and on UNIX, it is the system `ID`.

  Note: `ID` is case-sensitive to match your `ORACLE_SID`. `ROLE` is not case-sensitive.

- `ROLE` is the name of the database role.

- `d` is an optional character that indicates this role is to be a default role of the database user.

- `a` is an optional character that indicates this role is to be granted to the user with the `ADMIN OPTION`. This allows the user to grant the role to other roles only. Roles cannot be granted to users if the operating system is used to manage roles.

  Note: If either the `d` or `a` character is specified, then they must be preceded by an underscore.

For example, an operating system account might have the following roles identified in its profile:

    ora_PAYROLL_ROLE1
    ora_PAYROLL_ROLE2_a
    ora_PAYROLL_ROLE3_d
    ora_PAYROLL_ROLE4_da

When the corresponding user connects to the `payroll` instance of Oracle, `role3` and `role4` are defaults, while `role2` and `role4` are available with the `ADMIN OPTION`.

### 11.10.2 Using Operating System Role Management

When you use operating system managed roles, it is important to note that database roles are being granted to an operating system user. Any database user to which the operating system user is able to connect will have the authorized database roles enabled. For this reason, you should consider defining all Oracle users as `IDENTIFIED EXTERNALLY` if you are using `OS_ROLES = TRUE`, so that the database accounts are tied to the operating system account that was granted privileges.

### 11.10.3 Granting and Revoking Roles When OS_ROLES=TRUE

If `OS_ROLES` is set to `TRUE`, then the operating system completely manages the grants and revokes of roles to users. Any previous grants of roles to users using `GRANT` statements do not apply, however, they are still listed in the data dictionary. Only the role grants made at the operating system level to users apply. Users can still grant privileges to roles and users.

Note: If the operating system grants a role to a user with the `ADMIN OPTION`, then the user can grant the role only to other roles.

### 11.10.4 Enabling and Disabling Roles When OS_ROLES=TRUE

If `OS_ROLES` is set to `TRUE`, then any role granted by the operating system can be dynamically enabled using the `SET ROLE` statement. This still applies, even if the role was defined to require a password or operating system authorization. However, any role not identified in a user's operating system account cannot be specified in a `SET ROLE` statement, even if a role has been granted using a `GRANT` statement when `OS_ROLES = FALSE`. (If you specify such a role, then Oracle ignores it.)

When `OS_ROLES = TRUE`, a user can enable as many roles as specified by the initialization parameter `MAX_ENABLED_ROLES`.

### 11.10.5 Using Network Connections with Operating System Role Management

If you choose to have the operating system to manage roles, then by default users cannot connect to the database through the shared server. This restriction is the default because a remote user could impersonate another operating system user over a nonsecure connection.

If you are not concerned with this security risk and want to use operating system role management with the shared server, or any other network connection, then set the initialization parameter `REMOTE_OS_ROLES` in the database's initialization parameter file to `TRUE`. The change will take effect the next time you start the instance and mount the database. The default setting of this parameter is `FALSE`.

## 11.11 Viewing Privilege and Role Information

To access information about grants of privileges and roles, you can query the following data dictionary views:

View | Description
--- | ---
`DBA_COL_PRIVS` `ALL_COL_PRIVS` `USER_COL_PRIVS` | `DBA` view describes all column object grants in the database. `ALL` view describes all column object grants for which the current user or `PUBLIC` is the object owner, grantor, or grantee. `USER` view describes column object grants for which the current user is the object owner, grantor, or grantee.
`ALL_COL_PRIVS_MADE` `USER_COL_PRIVS_MADE` | `ALL` view lists column object grants for which the current user is object owner or grantor. `USER` view describes column object grants for which the current user is the grantor.
`ALL_COL_PRIVS_RECD` `USER_COL_PRIVS_RECD` | `ALL` view describes column object grants for which the current user or `PUBLIC` is the grantee. `USER` view describes column object grants for which the current user is the grantee.
`DBA_TAB_PRIVS` `ALL_TAB_PRIVS` `USER_TAB_PRIVS` | `DBA` view lists all grants on all objects in the database. `ALL` view lists the grants on objects where the user or `PUBLIC` is the grantee. `USER` view lists grants on all objects where the current user is the grantee.
`ALL_TAB_PRIVS_MADE` `USER_TAB_PRIVS_MADE` | `ALL` view lists the all object grants made by the current user or made on the objects owned by the current user. `USER` view lists grants on all objects owned by the current user.
`ALL_TAB_PRIVS_RECD` `USER_TAB_PRIVS_RECD` | `ALL` view lists object grants for which the user or `PUBLIC` is the grantee. `USER` view lists object grants for which the current user is the grantee.
`DBA_ROLES` | This view lists all roles that exist in the database.
`DBA_ROLE_PRIVS` `USER_ROLE_PRIVS` | `DBA` view lists roles granted to users and roles. `USER` view lists roles granted to the current user.
`DBA_SYS_PRIVS` `USER_SYS_PRIVS` | `DBA` view lists system privileges granted to users and roles. `USER` view lists system privileges granted to the current user.
`ROLE_ROLE_PRIVS` | This view describes roles granted to other roles. Information is provided only about roles to which the user has access.
`ROLE_SYS_PRIVS` | This view contains information about system privileges granted to roles. Information is provided only about roles to which the user has access.
`ROLE_TAB_PRIVS` | This view contains information about object privileges granted to roles. Information is provided only about roles to which the user has access.
`SESSION_PRIVS` | This view lists the privileges that are currently enabled for the user.
`SESSION_ROLES` | This view lists the roles that are currently enabled to the user.

Some examples of using these views follow. For these examples, assume the following statements have been issued:

    CREATE ROLE security_admin IDENTIFIED BY honcho;

    GRANT CREATE PROFILE, ALTER PROFILE, DROP PROFILE,
        CREATE ROLE, DROP ANY ROLE, GRANT ANY ROLE, AUDIT ANY,
        AUDIT SYSTEM, CREATE USER, BECOME USER, ALTER USER, DROP USER
        TO security_admin WITH ADMIN OPTION;

    GRANT SELECT, DELETE ON SYS.AUD$ TO security_admin;

    GRANT security_admin, CREATE SESSION TO swilliams;

    GRANT security_admin TO system_administrator;

    GRANT CREATE SESSION TO jward;

    GRANT SELECT, DELETE ON emp TO jward;

    GRANT INSERT (ename, job) ON emp TO swilliams, jward;

See Also: Oracle Database Reference for a detailed description of these data dictionary views

### 11.11.1 Listing All System Privilege Grants

The following query returns all system privilege grants made to roles and users:

    SELECT * FROM DBA_SYS_PRIVS;

    GRANTEE            PRIVILEGE                         ADM
    --------------     --------------------------------- ---
    SECURITY_ADMIN     ALTER PROFILE                     YES
    SECURITY_ADMIN     ALTER USER                        YES
    SECURITY_ADMIN     AUDIT ANY                         YES
    SECURITY_ADMIN     AUDIT SYSTEM                      YES
    SECURITY_ADMIN     BECOME USER                       YES
    SECURITY_ADMIN     CREATE PROFILE                    YES
    SECURITY_ADMIN     CREATE ROLE                       YES
    SECURITY_ADMIN     CREATE USER                       YES
    SECURITY_ADMIN     DROP ANY ROLE                     YES
    SECURITY_ADMIN     DROP PROFILE                      YES
    SECURITY_ADMIN     DROP USER                         YES
    SECURITY_ADMIN     GRANT ANY ROLE                    YES
    SWILLIAMS          CREATE SESSION                    NO
    JWARD              CREATE SESSION                    NO

### 11.11.2 Listing All Role Grants

The following query returns all the roles granted to users and other roles:

    SELECT * FROM DBA_ROLE_PRIVS;

    GRANTEE            GRANTED_ROLE                         ADM
    ------------------ ------------------------------------ ---
    SWILLIAMS          SECURITY_ADMIN                       NO

### 11.11.3 Listing Object Privileges Granted to a User

The following query returns all object privileges (not including column-specific privileges) granted to the specified user:

    SELECT TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS
        WHERE GRANTEE = 'JWARD';

    TABLE_NAME   PRIVILEGE    GRANTABLE
    -----------  ------------ ----------
    EMP          SELECT       NO
    EMP          DELETE       NO

To list all the column-specific privileges that have been granted, use the following query:

    SELECT GRANTEE, TABLE_NAME, COLUMN_NAME, PRIVILEGE
        FROM DBA_COL_PRIVS;

    GRANTEE      TABLE_NAME     COLUMN_NAME      PRIVILEGE
    -----------  ------------   -------------    --------------
    SWILLIAMS    EMP            ENAME            INSERT
    SWILLIAMS    EMP            JOB              INSERT
    JWARD        EMP            NAME             INSERT
    JWARD        EMP            JOB              INSERT

### 11.11.4 Listing the Current Privilege Domain of Your Session

The following query lists all roles currently enabled for the issuer:

    SELECT * FROM SESSION_ROLES;

If `swilliams` has enabled the `security_admin` role and issues this query, then Oracle Database returns the following information:

    ROLE
    ------------------------------
    SECURITY_ADMIN

The following query lists all system privileges currently available in the security domain of the issuer, both from explicit privilege grants and from enabled roles:

    SELECT * FROM SESSION_PRIVS;

If `swilliams` has the `security_admin` role enabled and issues this query, then Oracle returns the following results:

    PRIVILEGE
    ----------------------------------------
    AUDIT SYSTEM
    CREATE SESSION
    CREATE USER
    BECOME USER
    ALTER USER
    DROP USER
    CREATE ROLE
    DROP ANY ROLE
    GRANT ANY ROLE
    AUDIT ANY
    CREATE PROFILE
    ALTER PROFILE
    DROP PROFILE

If the `security_admin` role is disabled for `swilliams`, then the first query would return no rows, while the second query would only return a row for the `CREATE SESSION` privilege grant.

### 11.11.5 Listing Roles of the Database

The `DBA_ROLES` data dictionary view can be used to list all roles of a database and the authentication used for each role. For example, the following query lists all the roles in the database:

    SELECT * FROM DBA_ROLES;

    ROLE                  PASSWORD
    ----------------      --------
    CONNECT               NO
    RESOURCE              NO
    DBA                   NO
    SECURITY_ADMIN        YES

### 11.11.6 Listing Information About the Privilege Domains of Roles

The `ROLE_ROLE_PRIVS`, `ROLE_SYS_PRIVS`, and `ROLE_TAB_PRIVS` data dictionary views contain information on the privilege domains of roles. For example, the following query lists all the roles granted to the `system_admin` role:

    SELECT GRANTED_ROLE, ADMIN_OPTION
       FROM ROLE_ROLE_PRIVS
       WHERE ROLE = 'SYSTEM_ADMIN';

    GRANTED_ROLE              ADM
    ----------------          ----
    SECURITY_ADMIN            NO

The following query lists all the system privileges granted to the `security_admin` role:

    SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'SECURITY_ADMIN';

    ROLE                    PRIVILEGE                      ADM
    ----------------------- -----------------------------  ---
    SECURITY_ADMIN           ALTER PROFILE                 YES
    SECURITY_ADMIN           ALTER USER                    YES
    SECURITY_ADMIN           AUDIT ANY                     YES
    SECURITY_ADMIN           AUDIT SYSTEM                  YES
    SECURITY_ADMIN           BECOME USER                   YES
    SECURITY_ADMIN           CREATE PROFILE                YES
    SECURITY_ADMIN           CREATE ROLE                   YES
    SECURITY_ADMIN           CREATE USER                   YES
    SECURITY_ADMIN           DROP ANY ROLE                 YES
    SECURITY_ADMIN           DROP PROFILE                  YES
    SECURITY_ADMIN           DROP USER                     YES
    SECURITY_ADMIN           GRANT ANY ROLE                YES

The following query lists all the object privileges granted to the `security_admin` role:

    SELECT TABLE_NAME, PRIVILEGE FROM ROLE_TAB_PRIVS
        WHERE ROLE = 'SECURITY_ADMIN';

    TABLE_NAME                     PRIVILEGE
    ---------------------------    ----------------
    AUD$                           DELETE
    AUD$                           SELECT